using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;

namespace PCSComProduction.WorkOrder.DS
{
	/// <summary>
	/// Summary description for Pro_IssueSubMaterialDS.
	/// </summary>
	public class Pro_IssueSubMaterialMasterDS 
	{
		private const string THIS = "PCSComProduction.WorkOrder.DS.Pro_IssueSubMaterialMasterDS";
		public Pro_IssueSubMaterialMasterDS()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public Object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			DataSet dstPCS = new DataSet();
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ Pro_IssueSubMaterialMasterTable.BINID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.CCNID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.ISSUESUBMATERIALMASTERID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.LOCATIONID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.PARANTWODETAILID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.POSTDATE_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.PRODUCTIONLINEID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.SALEORDERDETAILID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.TRANNO_FLD
					+ " FROM " + Pro_IssueSubMaterialMasterTable.TABLE_NAME
					+" WHERE " + Pro_IssueSubMaterialMasterTable.ISSUESUBMATERIALMASTERID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_IssueSubMaterialMasterVO objObject = new PRO_IssueSubMaterialMasterVO();

				while (odrPCS.Read())
				{ 
					objObject.BinId = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.BINID_FLD].ToString().Trim());
					objObject.CCNID = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.CCNID_FLD].ToString().Trim());
					objObject.IssueSubMaterialMasterId = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.ISSUESUBMATERIALMASTERID_FLD].ToString().Trim());
					objObject.LocationId = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.LOCATIONID_FLD].ToString().Trim());
					try
					{
						objObject.ParentWoDetailId = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.PARANTWODETAILID_FLD].ToString().Trim());
					}
					catch 
					{
						objObject.ParentWoDetailId = 0;
					}
					objObject.PostDate = (DateTime) odrPCS[Pro_IssueSubMaterialMasterTable.POSTDATE_FLD];
					objObject.ProductionLineId = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.PRODUCTIONLINEID_FLD].ToString().Trim());
					objObject.SaleOrderDetailId = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.SALEORDERDETAILID_FLD].ToString().Trim());
					objObject.TransNo = odrPCS[Pro_IssueSubMaterialMasterTable.TRANNO_FLD].ToString().Trim();
					//objObject.WorkOrderDetailId = int.Parse(odrPCS[Pro_IssueSubMaterialMasterTable.WORKORDERDETAILID_FLD].ToString().Trim());

				}		
				return objObject;					
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataSet List()
		{
			throw new NotImplementedException();
		}

		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql=	"DELETE " + Pro_IssueSubMaterialMasterTable.TABLE_NAME + " WHERE  " + Pro_IssueSubMaterialMasterTable.ISSUESUBMATERIALMASTERID_FLD + "=" + pintID.ToString();
			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(Object pobjObjecVO)
		{
			throw new NotImplementedException();
		}

		public void Add(Object pobjObjectVO)
		{
			throw new NotImplementedException();
		}

		public void UpdateDataSet(DataSet pData)
		{
			throw new NotImplementedException();
		}

		public int AddAndReturnID(PRO_IssueSubMaterialMasterVO voIssueSubMaterialMaster)
		{
			const string METHOD_NAME = THIS + ".AddAndReturnID()";
			 
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS =null;
			try
			{
				PRO_IssueSubMaterialMasterVO objObject = voIssueSubMaterialMaster;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);
				
				strSql=	"INSERT INTO " + Pro_IssueSubMaterialMasterTable.TABLE_NAME + "("
					+ Pro_IssueSubMaterialMasterTable.BINID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.CCNID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.LOCATIONID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.PARANTWODETAILID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.POSTDATE_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.PRODUCTIONLINEID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.SALEORDERDETAILID_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.TRANNO_FLD + ","
					+ Pro_IssueSubMaterialMasterTable.WORKORDERDETAILID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?,?,?) ";

				strSql += "SELECT @@IDENTITY"; 

				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.BINID_FLD, OleDbType.Integer));
				if (objObject.BinId > 0)
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.BINID_FLD].Value = objObject.BinId;
				else
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.BINID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.LOCATIONID_FLD, OleDbType.Integer));
				if (objObject.LocationId > 0)
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.LOCATIONID_FLD].Value = objObject.LocationId;
				else
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.LOCATIONID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.PARANTWODETAILID_FLD, OleDbType.Integer));
				if (objObject.ParentWoDetailId > 0)
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.PARANTWODETAILID_FLD].Value = objObject.ParentWoDetailId;
				else
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.PARANTWODETAILID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.POSTDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.POSTDATE_FLD].Value = objObject.PostDate;
				
				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.PRODUCTIONLINEID_FLD, OleDbType.Integer));
				if (objObject.ProductionLineId > 0)
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.PRODUCTIONLINEID_FLD].Value = objObject.ProductionLineId;
				else
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.PRODUCTIONLINEID_FLD].Value = DBNull.Value;
				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
				if (objObject.SaleOrderDetailId > 0)
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.SALEORDERDETAILID_FLD].Value = objObject.SaleOrderDetailId;
				else
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.SALEORDERDETAILID_FLD].Value = DBNull.Value;
				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.TRANNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.TRANNO_FLD].Value = objObject.TransNo;
				
				ocmdPCS.Parameters.Add(new OleDbParameter(Pro_IssueSubMaterialMasterTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
				if (objObject.WorkOrderDetailId > 0)
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailId;
				else
					ocmdPCS.Parameters[Pro_IssueSubMaterialMasterTable.WORKORDERDETAILID_FLD].Value = DBNull.Value;
				
				ocmdPCS.CommandText = strSql;
				//ocmdPCS.CommandTimeout = 10000;
				ocmdPCS.Connection.Open();
				//ocmdPCS.ExecuteNonQuery();
				object objReturn = ocmdPCS.ExecuteScalar();
				if (objReturn != null)
				{
					return int.Parse(objReturn.ToString());
				}
				else
					return 0;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataTable GetMasterIssue(int pintIssueMasterID)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT ISMM.*, L.CODE LOCATION, B.CODE BIN , SO.FGoodsCode, P.Code, P.Description"
						+ " FROM PRO_IssueSubMaterialMaster ISMM"
						+ " LEFT JOIN MST_Location L ON ISMM.LocationID=L.LocationID "
						+ " LEFT JOIN MST_BIN B ON ISMM.BINID=B.BINID "
						+ " INner join SO_SaleOrderDetail SO ON SO.SaleOrderDetailID = ISMM.SaleOrderDetailID "
						+ " inner join ITM_Product P ON P.ProductID = SO.ProductID "
						+ " Where IssueSubMaterialMasterID = " + pintIssueMasterID.ToString();
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_IssueMaterialMasterTable.TABLE_NAME);

				return dstPCS.Tables[0];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}
